MySQL Functions (Usefull in Programming)

I am running on MySql 3.23.58 version

1 . Concat the String
SELECT CONCAT('My', 'S', 'QL');
-> MySQL
-----------

2. Concat with Word Separator
SELECT (CONCAT_WS(",","First name","Second name","Last Name")) as fullname;
-> First name,Second name,Last Name
-----------

3. String Length
SELECT LENGTH('text');
->4
-----------

3. String Padding (Add characters to string)
String must contin 4 character long if 1st_expr is not contain 4
charater then add 3rd_expr to String.
SELECT LPAD('hi',4,'..');
-> '..hi'
SELECT RPAD('hi',4,'..');
-> 'hi'
-----------

4. String Padding (Add character to string)
SELECT LEFT('foobarbar', 5);
->'fooba'
SELECT RIGHT('foobarbar', 5);
->'arbar'

COMBINATION OF 3 & 4 Point
SELECT RPAD(LEFT('foobarbar', 5),8,'.') as str;
-> fooba...

## Miscellaneous Functions

SELECT DATABASE();
-> current Working databse

SELECT USER();
-> curent User

SELECT SUBSTRING_INDEX(USER(),"@",1);
-> Concat userstring

SELECT VERSION();
->Current Mysql version

SELECT CONNECTION_ID();
-> Current connectio Id

MySql IFNULL, IF, CASE-WHEN-THEN -ELSE-END

IFNULL(expr,expr1)
--------------------------------------------
If 1st_ expr is null then retrun 2nd_expr

SELECT IFNULL(1,0);
-> 1
SELECT IFNULL(NULL,10);
-> 10
SELECT IFNULL(1/0,10);
-> 10
SELECT IFNULL(1/0,'yes');
-> 'yes'
--------------------------------------------------------------
CASE WHEN condiion THEN expr1 ELSE expr3 END
--------------------------------------------------------------

SELECT (CASE 2 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END) as ans;
-> two

SELECT CASE WHEN 1<0 style="color: rgb(255, 0, 0);">THEN "true" ELSE "false" END;
->false
--------------------------------------------------------------
IF(condition,expr,expr1)
--------------------------------------------------------------

SELECT IF(0.1<>0,1,0);
-> 1
SELECT IF(STRCMP('test','test1'),'no','yes');
-> no

How to create SubDomains by editing httpd.conf

VirtualHost:
If you want to maintain multiple domains/hostnames on your
machine you can setup VirtualHost containers for them.

Types of Virtual Hosting
1) IP-Based Virtual Hosting
2) Name-Based Virtual Hosting
3) Dynamic Virtual Hosting

Good Example how to set subDomains under NameBased Virtual Hosting :

NOTE: NameVirtualHost cannot be used without a port specifier
(e.g. :80) if mod_ssl is being used, due to the nature of the
SSL protocol.

Change Apache's httpd.conf file

NameVirtualHost 215.51.182.53

<VirtualHost 216.55.183.51>
ServerName *.yourdomain.com
ServerAlias yourdomain.com
DocumentRoot /home/rootdir #Directory where Scritpting files are located.
ErrorLog logs/error_log #Error are recorded in error_log file located /var/logs/httpd
CustomLog logs/visit_log combined # URL visit information recorded in visit_log file located /var/logs/httpd
</VirtualHost>

<VirtualHost 216.55.183.51>
ServerName subdomain.youydomain.com
ServerAlias subdomain.youydomain.com
DocumentRoot /home/rootdir/subdomain
ErrorLog logs/error_log
CustomLog logs/visit_log
</VirtualHost>

<VirtualHost 216.55.183.51>
ServerName subdomain1.youydomain.com
ServerAlias subdomain1.youydomain.com
DocumentRoot /home/rootdir/subdomain
ErrorLog logs/error_log
CustomLog logs/visit_log
</VirtualHost>

NOTE :
You can create n number of SubDoamins on one IP address

Apache's Module (Somthing useful in httpd.conf for Web Programmer)

If you want to include any New settings whithout editing
httpd.conf file then copy following line once into your server
httpd.conf file. And later if you want to add new Module then edit subdomain.conf
and restart the server.
#------------------------------------------
Include /home/jyve_conf/subdomain.conf
#------------------------------------------


DirectoryIndex: sets the file that Apache will serve if a directory
is requested. If you requested http://yourdomain.com then server will automatically search index.html first for default file if not found then search for any mathch in the follwing list. You can list any file which you want to serve as default when user request for directory
#-------------------------------------------------------
DirectoryIndex index.html index.html.var index.php3
#-------------------------------------------------------


if your .htaccess is file is not working then add following lines in httpd.conf
(red) is folder path where .htaccess file located.
#-------------------------------------------------------------
<Directory /home/vikrant/userprofile>
AllowOverride All
</Directory>
#-------------------------------------------------------------

Important Linux Commands

Tar , UnTar and UnZip
--------------------------------
tar -zxf 18012005.tar vikrant/
tar -zcf newtitle.tar *.php
unzip myzip.zip

Folder SIZE
----------------
du -skh * | less -- Check the folder size
du -skh *| grep M | less -- Check the folder size which has MB

SCP:
-------------
-->Upload File to Server
# scp config.php username@www.youdomain.com:/home/rootdir/filename.php

-->Download file from server on Currnet directory.
# scp username@www.youdomain.com:/home/rootdir/filename.php .

Fetch (Download files from server)
-----------------------------------------
#wget http://yuordomain.com .

List Contents of Directory
---------------------------------
ls -l | wc -- (Get Number of Files /Content)
ls -lah -- (Human redable file size)

Check ServerLoad
----------------------
w

CronTab Quick Reference

What is Cron Tab ?
cron is a unix utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon often termed as cron jobs.
Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

Restriction
You can execute crontab if your name appears in the file
/usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file
/usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

Commands
export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn't already exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)

Crontab File
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.
* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (1 - 7) (monday = 1)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

Environment
cron invokes the command from the user's HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user's-home-directory
LOGNAME=user's-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so
in the crontab entry or in a script called by the entry.

Disable Email
By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

Generate Log File
To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log

Examples :
Cron set For Database BackUp

5 21 * * * mysqldump -u root -pclarion --opt specto | gzip > /backup1/amodd/spec
to_`date +%Y%m%d%H%M`.gz

Cron Set for run PHP File ..
* 0,6,12,18 * * * /usr/bin/php -q /var/www/html/vikrant/goskype/expertblog/blogsadmin/runcron.php > /dev/null

HTTP Authentication to Folder. (.htaccess /.htpasswd)

Write Following code in .htaccess file and save it into the folder which you want to
authonticate.

Change the path (In red) and enter the Folder path which you want to authenticate.

------------------------------------------------------------------

AuthUserFile /mnt/web/guide/somewhere/somepath/.htpasswd
AuthGroupFile /dev/null
AuthName "Somewhere.com's Secret Section"
AuthType Basic
<Limit GET POST>
require valid-user
</Limit>

--------------------------------------------------------------------
To create an .htpasswd file, login to the server via telnet(Command line) go to the directory you specified in AuthUserFile. In the example, this is /mnt/web/guide/somewhere/somepath. Then use the htpasswd program with the -c switch to create your .htpasswd in the current directory. (as per following)

----------------------------------------
htpasswd -c .htpasswd username
Adding password for username.
New password:
password
Re-type new password:
password
---------------------------------------

To delete users, open the .htpasswd file in a text editor and delete the appropriate lines:

Execute PHP syntax in .HTML file

If you want to run PHP code in .html extention file
Then Follow following procedures.

1. Write Following code in the .htaccess file
---------------------------------------------
RemoveHandler .html .htm
AddType application/x-httpd-php .php .htm .html
-----------------------------------------------------

2. Upload it to your server (to your WWW root) where your
.html files are located in which contains PHP code.

Thats the idea.